package com.ets.Codes_DAO;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import com.ets.common.DateUtils;
import com.ets.database.DB_Pipe;

/*------------------------------------------------------------------------------
 The <big><b>CodeValueDB</b></big> class implements the reader & writer methods
 of the <b>CodeValueDAO</b> interface to provide the data access to the
 <big><b>CodeValue</b></big> table
 <br><br>
 @author D.Merkling - 1:33:38 PM - Aug 22, 2011
 <br><br>
 @version 1.2  
------------------------------------------------------------------------------*/
public class CodeValueDB extends DB_Pipe implements CodeValueDAO
  {
   public CodeValueDB ()
	 {
	 }

   /*------------------------------------------------------------------------------
	CodeValue reader methods
   ------------------------------------------------------------------------------*/
   
   public boolean existsCode (String Sys, String Sgmt, String Cd)
	 {
	  String tmpSys  = Sys.toUpperCase();
	  tmpSys         = tmpSys.trim();
	  String tmpSgmt = Sgmt.toUpperCase();
	  tmpSgmt        = tmpSgmt.trim();
	  String tmpCd   = Cd.toUpperCase();
	  tmpCd          = tmpCd.trim();
	  try
		{
	     String selCodeValue = "SELECT Val "      +
				                 "FROM cd_val "   +
				                "WHERE Sys  = ? " +
				                "  AND Sgmt = ? " +
				                "  AND Cd   = ?";
         DB_Pipe              = DB_Connect();
	     PreparedStatement ps = DB_Pipe.prepareStatement(selCodeValue);
		 ps.setString(1, tmpSys);
		 ps.setString(2, tmpSgmt);
		 ps.setString(3, tmpCd);
		 ResultSet rs = ps.executeQuery();
		 if (rs.next())
		   {
		    rs.close();
		    ps.close();
		    return true;
		   }
		 else
		   return false;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
	 }

   public CodeValue getCodeValue (String Sys, String Sgmt, String Cd)
	 {
	  String tmpSys  = Sys.toUpperCase();
	  tmpSys         = tmpSys.trim();
	  String tmpSgmt = Sgmt.toUpperCase();
	  tmpSgmt        = tmpSgmt.trim();
	  String tmpCd   = Cd.toUpperCase();
	  tmpCd          = tmpCd.trim();
	  try
		{
	     String selCodeValue = "SELECT Sys, "     +
		                              "Sgmt, "    +
		                              "Cd, "      +
		                              "Val, "     +
		                              "Val2 "     +
				                 "FROM cd_val "   +
				                "WHERE Sys  = ? " +
				                "  AND Sgmt = ? " +
				                "  AND Cd   = ?";
         DB_Pipe              = DB_Connect();
	     PreparedStatement ps = DB_Pipe.prepareStatement(selCodeValue);
		 ps.setString(1, tmpSys);
		 ps.setString(2, tmpSgmt);
		 ps.setString(3, tmpCd);
		 ResultSet rs = ps.executeQuery();
		 if (rs.next())
		   {
		    String Val   = rs.getString("Val");
	        String Val2  = rs.getString("Val2");
		    CodeValue cv = new CodeValue(Sys, Sgmt, Cd, Val, Val2);
		    rs.close();
		    ps.close();
		    return cv;
		   }
		 else
		   return null;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return null;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
	 }

   public ArrayList<CodeValue> getCodeValues ()
	 {
	  try
		{
		 ArrayList<CodeValue> cvs = new ArrayList<CodeValue>();
		 String query = "SELECT Sys, "         +
		                       "Sgmt, "        +
		                       "Cd, "          +
		                       "Val, "         +
		                       "Val2 "         +
			 			  "FROM cd_val "       +
					     "ORDER BY Sys  ASC, " +
			                      "Sgmt ASC, " +
					              "Cd   ASC";
         DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(query);
		 ResultSet         rs = ps.executeQuery();
		 while(rs.next())
		   {
		    String Sys   = rs.getString("Sys");
		    String Sgmt  = rs.getString("Sgmt");
		    String Cd    = rs.getString("Cd");
		    String Val   = rs.getString("Val");
		    String Val2  = rs.getString("Val2");
		    CodeValue cv = new CodeValue(Sys, Sgmt, Cd, Val, Val2);
		    cvs.add(cv);
		   }
		 rs.close();
		 ps.close();
		 return cvs;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();  // for debugging
		 return null;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
	 }
   
   public ArrayList<CodeValue> getCodeValuesByType (String Sys, String Sgmt)
	 {
	  String tmpSys  = Sys.toUpperCase();
	  tmpSys         = tmpSys.trim();
	  String tmpSgmt = Sgmt.toUpperCase();
	  tmpSgmt        = tmpSgmt.trim();
	  try
		{
		 ArrayList<CodeValue> cvs = new ArrayList<CodeValue>();
		 String query = "SELECT Sys, "         +
		                       "Sgmt, "        +
		                       "Cd, "          +
		                       "Val, "         +
		                       "Val2 "         +
			 			  "FROM cd_val "       +
			 			 "WHERE Sys  = ? "     +
			 			 "  AND Sgmt = ? "     +
					     "ORDER BY Sys  ASC, " +
			                      "Sgmt ASC, " +
					              "Cd   ASC";
         DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(query);
		 ps.setString(1, tmpSys);
		 ps.setString(2, tmpSgmt);
		 ResultSet         rs = ps.executeQuery();
		 while(rs.next())
		   {
		    String inSys   = rs.getString("Sys");
		    String inSgmt  = rs.getString("Sgmt");
		    String inCd    = rs.getString("Cd");
		    String inVal   = rs.getString("Val");
		    String inVal2  = rs.getString("Val2");
		    CodeValue cv = new CodeValue(inSys, inSgmt, inCd, inVal, inVal2);
		    cvs.add(cv);
		   }
		 rs.close();
		 ps.close();
		 return cvs;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();  // for debugging
		 return null;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
	 }

   /*------------------------------------------------------------------------------
	CodeValue writer methods
   ------------------------------------------------------------------------------*/
   
   public boolean addCodeValue (CodeValue cv)
	 {
	  try
		{
		 String insert   = "INSERT "                    +
		                     "INTO cd_val (Sys, "       +
		                                  "Sgmt, "      +
		                                  "Cd, "        +
		                                  "Val, "       +
		                                  "Val2, "      +
		                                  "TMS_Updt, "  +
		                                  "User_Updt, " +
		                                  "TMS_Crtd, "  +
		                                  "User_Crtd) " +
				           "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
         DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(insert);
	     Timestamp         ts = DateUtils.getTmstmp();
		 ps.setString(1, cv.getSys());
		 ps.setString(2, cv.getSgmt());
		 ps.setString(3, cv.getCd());
		 ps.setString(4, cv.getVal());
		 ps.setString(5, cv.getVal2());
		 ps.setTimestamp(6, ts);
		 ps.setString(7, "USER0001");
		 ps.setTimestamp(8, ts);
		 ps.setString(9, "USER9999");
		 ps.executeUpdate();
		 ps.close();
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
	 }

   public boolean deleteCodeValue (CodeValue cv)
	 {
	  try
		{
		 String delete = "DELETE "          +
		                   "FROM cd_val "   +
                          "WHERE Sys  = ? " +
                          "  AND Sgmt = ? " +
                          "  AND Cd   = ?";
         DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(delete);
		 ps.setString(1, cv.getSys());
		 ps.setString(2, cv.getSgmt());
		 ps.setString(3, cv.getCd());
		 ps.executeUpdate();
		 ps.close();
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
	 }

   public boolean updateCodeValue (CodeValue cv)
	 {
	  try
		{
		 String update = "UPDATE cd_val "         +
		                    "SET Val       = ?, " +
		                        "Val2      = ?, " +
		                        "TMS_Updt  = ?, " +
					            "User_Updt = ? "  +
                          "WHERE Sys  = ? "       +
                          "  AND Sgmt = ? "       +
                          "  AND Cd   = ?";
         DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(update);
	     Timestamp         ts = DateUtils.getTmstmp();
		 ps.setString(1, cv.getVal());
		 ps.setString(2, cv.getVal2());
		 ps.setTimestamp(3, ts);
		 ps.setString(4, "USER8888");
		 ps.setString(5, cv.getSys());
		 ps.setString(6, cv.getSgmt());
		 ps.setString(7, cv.getCd());
		 ps.executeUpdate();
		 ps.close();
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
	 	 return false;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
	 }
  }
